﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mono.Data.Sqlite;

namespace XYZShell.CsHelper.DB
{
    public class SqliteHelper:IDisposable
    {
        public SqliteHelper()
        {
        }
        public SqliteHelper(string dbName)
            :base()
        {
            this.DbName = dbName;
        }       
        
        public string DbName { get; set; }
        public void CreateDB(string dbName)
        {
            if (!System.IO.File.Exists(dbName))
            {
                SqliteConnection.CreateFile(dbName);
                this.DbName = dbName;
                
            }            
        }
        //config:Data Source= E:\file.db
        SqliteConnection sqlitecon = new SqliteConnection();
        SqliteCommand sqlitecmd = new SqliteCommand();
        SqliteDataAdapter sqliteda = new SqliteDataAdapter();
        public void CreateTable(Type type)
        { 

        }
        private void open()
        {
            if (sqlitecon.State == System.Data.ConnectionState.Closed)
            {
                sqlitecon.Open();
            }
        }
        private void close()
        {
            if (sqlitecon.State == System.Data.ConnectionState.Open)
            {
                sqlitecon.Close();
            }
        }
        public void SetSql(string sql)
        {
            sqlitecon.ConnectionString = string.Format("Data Source = {0}",DbName);
            sqlitecmd.CommandText = sql;
            sqlitecmd.Connection = sqlitecon;
            sqlitecmd.Parameters.Clear();
            open();
        }

        public void SetPamar(string key, object value)
        {
            SqliteParameter sqlitepar = new SqliteParameter(key, value);
            sqlitecmd.Parameters.Add(sqlitepar);
        }

        public System.Data.DataSet GetDataSet()
        {
            try
            {
                System.Data.DataSet ds = new System.Data.DataSet();
                sqliteda.SelectCommand = sqlitecmd;
                sqliteda.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                close();
            }
        }


        public System.Data.DataTable GetData()
        {
            return GetDataSet().Tables[0];
        }
        
        public List<T> Query<T>()
        { 
            Type type = typeof(T);
            SqliteDataReader reader = sqlitecmd.ExecuteReader();
            List<T> results = new List<T>();
            if (reader!=null)
            {
                while (reader.Read())
                {
                    T obj = Activator.CreateInstance<T>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string fledName = reader.GetName(i);
                        object val = reader.GetValue(i);
                        System.Reflection.PropertyInfo pi = type.GetProperty(fledName);
                        pi.SetValue(obj, val, null);
                    }
                    results.Add(obj);
                }
            }
            return results;
        }

        public int ExeSql()
        {
            try
            {
                return sqlitecmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                close();
            }
        }

        public string sqlprint()
        {
            string sqlstr = sqlitecmd.CommandText;
            for (int i = 0; i < sqlitecmd.Parameters.Count; i++)
            {
                sqlstr = sqlstr.Replace(sqlitecmd.Parameters[i].ToString(), "'" + sqlitecmd.Parameters[i].Value + "'");
            }
            return sqlstr;
        }
        public void Dispose()
        {
            sqlitecmd.Dispose();
            sqlitecon.Dispose();
        }
    }
}
